package SQL;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import javax.swing.JOptionPane;

import org.omg.CORBA.FloatHolder;

import java.util.Map.Entry;

import com.fasterxml.jackson.core.JsonParseException;
import com.fasterxml.jackson.databind.JsonMappingException;
import com.fasterxml.jackson.databind.ObjectMapper;

import Entity.HostTraffic;
import Entity.Test;

public class DataCURD{
	String se = File.separator;
    static String JDBC_DRIVER = "";  
    static String DB_URL = "";  
    static String USER = "";  
    static String PASS = "";  
	static Config config = new Config() ;
    
    //数据库连接参数初始化
	public void initConfig() throws IOException {
		Properties properties = config.getConfig("src/SQL/db.properties");
//		Properties properties = config.getConfig("."+se+"db.properties");//内网线上
//		Properties properties = config.getConfig(se+"tsgz"+se+"db.properties");//外网线上
		// JDBC 驱动名及数据库 URL
	      JDBC_DRIVER = properties.getProperty("JDBC_DRIVER");  
	      DB_URL = properties.getProperty("DB_URL");
	      USER = properties.getProperty("USER");
	      PASS = properties.getProperty("PASS");
	}
	
	// JDBC 驱动名及数据库 URL
//    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";  
////  static final String DB_URL = "jdbc:mysql://172.30.194.196:3306/sas";
//    static final String DB_URL = "jdbc:mysql://localhost:3306/syslog";
////  static final String USER = "mild";
////  static final String PASS = "dEce1veDBylIFe";
//    static final String USER = "root";
//    static final String PASS = "123456";

    


    //查询场景返回结果
    List<?> orders = new ArrayList<>();
	
    
    //查询
    public StringBuffer findSession(String sql) {
    	
        StringBuffer sb=new StringBuffer();
        Connection conn = null;
        Statement stmt = null;
    	
        try{
            // 注册 JDBC 驱动
            Class.forName(JDBC_DRIVER);
        
            // 打开链接
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
        
            // 执行查询
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);

			
			ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等   
	        int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数     
	        ObjectMapper mapper = new ObjectMapper();  
            while(rs.next()){
    	        Map<String, Object> rowData = new HashMap<String, Object>();  
    	        rowData = new HashMap<String, Object>(columnCount);   
    	        for (int i = 1; i <= columnCount; i++) {   
    	            rowData.put(md.getColumnName(i), rs.getObject(i));   
    	        }       
    			String json = mapper.writeValueAsString(rowData);
    			System.out.println(json);
    			sb.append(json);
    			sb.append("\r\n");
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        return sb;
    }
    

    /** 
	 *  批量保存数据的通用方法
	 * @param tablename 目标表 
	 * @param lines 被写入的数据
     * @throws SQLException 
     * @throws ClassNotFoundException 
	 */  
	public void saveForAny(List<Map> lines,String tablename) throws SQLException, ClassNotFoundException {
	    Connection conn = null;
	    Statement stmt = null;
        // 注册 JDBC 驱动
        Class.forName(JDBC_DRIVER);
        // 打开链接
        conn = DriverManager.getConnection(DB_URL,USER,PASS);
	       try{
	            getAndInsertData(tablename, lines, stmt, conn);
	            if (conn!=null) {}conn.commit();
	            if (conn!=null) {}conn.close();
	        }catch(SQLException se){
	            // 处理 JDBC 错误
	            se.printStackTrace();
	        }catch(Exception e){
	            // 处理 Class.forName 错误
	            e.printStackTrace();
	        }finally{
	            try{
	                if(conn!=null) conn.close();
	            }catch(SQLException se){
	                se.printStackTrace();
	            }
	        } 
	}
	
    /** 
	 *  向数据库插入数据，要求目的数据库至少有一条数据
	 * @param tablename 目标表 
	 * @param lines 被写入的数据
     * @throws SQLException 
     * @throws IOException 
     * @throws JsonMappingException 
     * @throws JsonParseException 
	 */  
	public void getAndInsertData(String tablename,List<Map> lines, Statement stmt,Connection conn) throws SQLException, JsonParseException, JsonMappingException, IOException {
		SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		boolean label = true;//数据源标识字段，true：源为lines;false:源为lines中的某一个字段
		String table = null;//表名
		String col = null;//字段名
		String fatherTable = null;
		//根据id判断进行数据过滤。
		
		List<Map> newlines = new ArrayList<Map>();
		
		if (tablename.contains(".")) {
			fatherTable = tablename.split("\\.")[0];
			table = fatherTable+"_"+tablename.split("\\.")[1];
			col = tablename.split("\\.")[1];
			label = false;
			
		} else {
			table = tablename;
		}
		
		if (label) {
			Long maxId = findId("SELECT id FROM "+table+" ORDERN BY id DESC LIMIT 1");
			for (Map line : newlines) {
				if (new Integer((int)line.get("id")).longValue()>maxId) {
					newlines.add(line);
				}
			}
		} else {
			Long maxId = findIdNew("SELECT "+fatherTable+"_id"+" FROM "+table+" ORDERN BY "+fatherTable+"_id"+" DESC LIMIT 1",fatherTable+"_id");
			for (Map line : newlines) {
				if (new Integer((int)line.get("id")).longValue()>maxId) {
					newlines.add(line);
				}
			}
			
		}

        ResultSet rs = stmt.executeQuery("select * from "+tablename+" limit 1");

		
		ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等   
        int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数  
        
        ObjectMapper mapper = new ObjectMapper();  
        Map<Integer, Object> typeData = new HashMap<Integer, Object>();  //存储表的的列类型数据
        typeData = new HashMap<Integer, Object>(columnCount);
        Map<Integer, Object> nameData = new HashMap<Integer, Object>();  //存储表的的列类型数据
        nameData = new HashMap<Integer, Object>(columnCount);

        String add = "insert into "+tablename; //需要被执行的sql语句。
        String columnName = ""; //拼接字段
        String column = ""; //拼接占位"?"
        
        //获取表的列名、类型信息
        while(rs.next()){  
	        for (int i = 1; i <= columnCount; i++) {
	        	typeData.put(i, md.getColumnTypeName(i)); //保存类型和序号关系数据
	        	nameData.put(i, md.getColumnName(i)); //保存类型和序号关系数据
	        	if (i==1) {
    	        	columnName += md.getColumnName(i);
    	        	column += "?";
				} else {
    	        	columnName += ","+md.getColumnName(i);
    	        	column += ",?";
				}
//	            System.out.println(md.getColumnName(i)+":"+md.getColumnTypeName(i)+":"+rs.getObject(i));
	        }       
        }
        
         add +=  " ("+columnName+") values("+column+") ";
//         System.out.println("插入的sql："+add);
         //创建连接池.
         PreparedStatement ps = conn.prepareStatement(add);
         conn.setAutoCommit(false);

         for (Map line: newlines) {
        	 
        	 //如果数据源是字段，重新给line赋值
        	 if (!label) {
        		 Map line1 = new HashMap<>();
        		 
        		 //获取指定字段的值，转成map。
        		 ObjectMapper mapper1 = new ObjectMapper();
        		 if (line.get(col)!=null) {
        			line1 = mapper1.readValue((String)line.get(col), Map.class);
        			line1.put(fatherTable+"_id", line.get("id"));
					line = line1;
				} else {
					line1 = new HashMap<>();
        			line1.put(fatherTable+"_id", line.get("id"));
					line = line1;
				}
			}
        	 
        	 
         	 for (Entry<Integer, Object> entry : typeData.entrySet()) {
         		 Integer key = entry.getKey();
         		 int key1 = key.intValue();
         		 String val = entry.getValue().toString().split(" ")[0];
                  if("VARCHAR".equals(val)||"TEXT".equals(val)){  
                	if (line.get((nameData.get(key1)==null)?"!@#$%^&*()&%":nameData.get(key1))!=null) {
                      	ps.setString((int)key1, (String)line.get(nameData.get(key1)).toString());  
//						System.out.println(key1);
					} else {
	                 	 ps.setString((int)key1, "");  
//						System.out.println(key1);
					}
                  } 
                  if("BIGINT".equals(val)||"INT".equals(val)||"TINYINT".equals(val)||"SMALLINT".equals(val)){  
                	if (line.get((nameData.get(key1)==null)?"!@#$%^&*()&%":nameData.get(key1))!=null) {
                		if (line.get(nameData.get(key1)).getClass()==Long.class) {
							ps.setLong(key1, (long)line.get(nameData.get(key1)));
						} else {
							ps.setInt(key1, (int)line.get(nameData.get(key1)));
						}
//						System.out.println(key1);
					} else {
	                 	 ps.setLong(key1, 0);  
//						System.out.println(key1);
					}
                  } 
                  if("DATETIME".equals(val)){  
                	if (line.get((nameData.get(key1)==null)?"!@#$%^&*()&%":nameData.get(key1))!=null) {
                    	 ps.setTimestamp(key1, new Timestamp((line.get(nameData.get(key)).getClass()==long.class)?(long) (line.get(nameData.get(key))):(long)line.get(nameData.get(key1)))); 
//						System.out.println(key1);
					} else {
	                 	 ps.setTimestamp(key1, new Timestamp(0));  
//						System.out.println(key1);
					} 
                  }  
              }  
         	ps.addBatch();
			}
         ps.executeBatch();
	}
	
	//获取最新id
    public long findIdNew(String sql,String ids) {
	    Connection conn = null;
	    Statement stmt = null;
    	long id =0L;
        try{
            // 注册 JDBC 驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 打开链接
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            // 执行查询
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
            	id = rs.getLong(ids);
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        return id;
    }
    
    
	//获取最新id
    public long findId(String sql) {
	    Connection conn = null;
	    Statement stmt = null;
    	long id =0L;
        try{
            // 注册 JDBC 驱动
            Class.forName("com.mysql.jdbc.Driver");
            // 打开链接
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            // 执行查询
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()){
            	id = rs.getLong("id");
            }
            rs.close();
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
        return id;
    }
    
    //批量保存
    /** 
	 *  批量保存数据
	 * @param tablename 表 
	 * @param datas 被写入的数据
	 */  
	public void save(List<Test> tests,String tablename) {
	    Connection conn = null;
	    Statement stmt = null;
	       try{
	            // 注册 JDBC 驱动
	            Class.forName("com.mysql.jdbc.Driver");
	            conn = DriverManager.getConnection(DB_URL,USER,PASS);
	            String add = "insert into "+tablename + "(id,name,date,time) values(?,?,?,?)";
	            //创建连接池.
	            PreparedStatement ps = conn.prepareStatement(add);
	            conn.setAutoCommit(false);
	            for (Test test: tests) {
	            	ps.setInt(1, test.getId());
	            	ps.setString(2, test.getName());
	            	ps.setTimestamp(3, test.getDate());
	            	ps.setTimestamp(4, test.getTime());
	            	ps.addBatch();
				}
	            ps.executeBatch();
	            conn.commit();
	            conn.close();
	        }catch(SQLException se){
	            // 处理 JDBC 错误
	            se.printStackTrace();
	        }catch(Exception e){
	            // 处理 Class.forName 错误
	            e.printStackTrace();
	        }finally{
	            try{
	                if(conn!=null) conn.close();
	            }catch(SQLException se){
	                se.printStackTrace();
	            }
	        } 
	}
    
    //保存
    public void saveSession(String sql) {
	    Connection conn = null;
	    Statement stmt = null;
        try{
            // 注册 JDBC 驱动
            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
        
            stmt = conn.createStatement();
            stmt.executeUpdate(sql);
            stmt.close();
            conn.close();
        }catch(SQLException se){
            // 处理 JDBC 错误
            se.printStackTrace();
        }catch(Exception e){
            // 处理 Class.forName 错误
            e.printStackTrace();
        }finally{
            // 关闭资源
            try{
                if(stmt!=null) stmt.close();
            }catch(SQLException se2){
            }
            try{
                if(conn!=null) conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }
        }
    }
}
